﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Utility;

namespace BLL
{
    public class QS_PROJECTBLL : BaseBLL
    {
        public QS_PROJECTBLL(Service service)
        {
            this.Service = service;
        }

        public QS_PROJECTBLL()
        {
        }

        /// <summary>
        /// 绑定GridView数据
        /// </summary>
        /// <returns></returns>
        public DataTable SelectBy(string whr)
        {
            string sql = "";

            sql = @"select t.n_proid,
       t.c_proname,
       decode(t.n_state, 1, '有效', '无效') state,
       t.n_num1,
       t.n_num2,
       t.c_remark,
       t.n_parentid,
       t.n_type,
       t.c_pronum,
       decode(t.n_type, 0, '主节点', 1, '分节点', 2, '子节点') n_type2
  from qs_project t where 1=1 " + whr + @"
 order by t.n_parentid, t.n_num1, t.n_num2";

            DataTable dt = base.Query(sql, "", "", "", "");

            return dt;
        }

        /// <summary>
        /// 向下递归查询
        /// </summary>
        /// <param name="whr"></param>
        /// <param name="logname"></param>
        /// <returns></returns>
        public DataTable SelectFor(string whr, string pid, string logname)
        {
            string sql = @"select d.n_proid,d.c_proname,d.n_parentid
  from qs_project d
 where 1=1 " + whr + @"
 start with d.n_proid = " + pid + @"
connect by prior d.n_proid = d.n_parentid";

            DataTable dt = base.Query(sql, "", "", "", logname);
            return dt;
        }

        public QS_PROJECT GetModelById(string id)
        {
            var sql = "SELECT * FROM QS_PROJECT WHERE N_PROID = {0}";
            sql = string.Format(sql, id);
            var row = RowSingeleOfNull(sql);
            if (row == null)
            {
                return null;
            }
            return ToEntity(row);
        }

        public QS_PROJECT Find(decimal id)
        {
            return GetModelById(id.ToString());
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model"></param>
        /// <param name="win"></param>
        /// <param name="logname"></param>
        /// <returns></returns>
        public int insert(QS_PROJECT model, string win, string logname)
        {
            model.N_PROID = GetSequence("SEQ_N_PROID");
            var sql = @"INSERT INTO QS_PROJECT(
	N_PROID,
	C_PRONAME,
	N_OPERATORID,
	D_DATE,
	C_REMARK,
	N_PARENTID,
	N_NUM1,
	N_NUM2,
	N_STATE,
	N_TYPE,
	C_PRONUM,
	CMY_ID,
	N_TYPE2,C_CODE1,C_CODE2,C_CODE3,C_CODE4
)
VALUES(
	{0},
	'{1}',
	{2},
	TO_DATE('{3}','YYYY-MM-DD HH24:MI:SS'),
	'{4}',
	{5},
	{6},
	{7},
	{8},
	{9},
	'{10}',
	{11},
	{12},'{13}','{14}','{15}','{16}'
)";
            sql = string.Format(sql, model.N_PROID, model.C_PRONAME, model.N_OPERATORID,
                model.D_DATE.ToString("yyyy-MM-dd HH:mm:ss"), model.C_REMARK.ToString(), model.N_PARENTID,
                model.N_NUM1, model.N_NUM2, model.N_STATE,
                model.N_TYPE, model.C_PRONUM, model.CMY_ID,
                model.N_TYPE2, model.C_CODE1, model.C_CODE2, model.C_CODE3, model.C_CODE4
                );
            return NoQuery(sql, logname, win, "insert");
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <param name="win"></param>
        /// <param name="logname"></param>
        /// <returns></returns>
        public int Update(QS_PROJECT model, string win, string logname)
        {
            var sql = @"UPDATE QS_PROJECT
SET
	C_PRONAME = '{1}',
	N_OPERATORID = {2},
	D_DATE = TO_DATE('{3}','YYYY-MM-DD HH24:MI:SS'),
	C_REMARK = '{4}',
	N_PARENTID = {5},
	N_NUM1 = {6},
	N_NUM2 = {7},
	N_STATE = {8},
	N_TYPE = {9},
	C_PRONUM = '{10}',
	CMY_ID = {11},
	N_TYPE2 = {12},
    C_CODE1='{13}',
    C_CODE2='{14}',
    C_CODE3='{15}',
    C_CODE4='{16}'
WHERE
	N_PROID = {0}";
            sql = string.Format(sql, model.N_PROID, model.C_PRONAME, model.N_OPERATORID,
                model.D_DATE.ToString("yyyy-MM-dd HH:mm:ss"), model.C_REMARK.ToString(), model.N_PARENTID,
                model.N_NUM1, model.N_NUM2, model.N_STATE,
                model.N_TYPE, model.C_PRONUM, model.CMY_ID,
                model.N_TYPE2, model.C_CODE1, model.C_CODE2, model.C_CODE3, model.C_CODE4
                );
            return NoQuery(sql, win, "Update", logname);
        }

        public List<QS_PROJECT> SelectByPage(string whereStr, string orderbyStr, int skip, int take, out int count)
        {
            var dt = SelectByPageData(whereStr, orderbyStr, skip, take, out count);
            var entitys = new List<QS_PROJECT>();
            foreach (DataRow item in dt.Rows)
            {
                entitys.Add(ToEntity(item));
            }
            return entitys;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="whereStr">条件 可为NULL</param>
        /// <param name="orderbyStr">排序 可为NULL</param>
        /// <param name="skip"></param>
        /// <param name="take"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public DataTable SelectByPageData(string whereStr, string orderbyStr, int skip, int take, out int count)
        {
            var sql = "SELECT * FROM QS_PROJECT";
            if (whereStr != null)
            {
                sql += " WHERE " + whereStr;
            }
            if (orderbyStr != null)
            {
                sql += " ORDER BY " + orderbyStr;
            }
            count = SqlCount(sql);
            return PageQuery(sql, skip, take);
        }

        public QS_PROJECT ToEntity(DataRow row)
        {
            var entity = new QS_PROJECT
            {
                N_PROID = Convert.ToDecimal(row["N_PROID"]),
                C_PRONAME = Convert.ToString(row["C_PRONAME"]),
                N_OPERATORID = Convert.ToDecimal(row["N_OPERATORID"]),
                D_DATE = Convert.ToDateTime(row["D_DATE"]),
                C_REMARK = Convert.ToString(row["C_REMARK"]),
                N_PARENTID = Convert.ToDecimal(row["N_PARENTID"]),
                N_NUM1 = Convert.ToDecimal(row["N_NUM1"]),
                N_NUM2 = Convert.ToDecimal(row["N_NUM2"]),
                N_STATE = Convert.ToDecimal(row["N_STATE"]),
                N_TYPE = Convert.ToDecimal(row["N_TYPE"]),
                C_PRONUM = row["C_PRONUM"] == DBNull.Value ? null : Convert.ToString(row["C_PRONUM"]),
                CMY_ID = Convert.ToDecimal(row["CMY_ID"]),
                N_TYPE2 = Convert.ToDecimal(row["N_TYPE2"]),
                N_IF_DETAILS = row["N_IF_DETAILS"] == DBNull.Value ? 0 : Convert.ToDecimal(row["N_IF_DETAILS"]),
                C_DETAILS_CODE = row["C_DETAILS_CODE"] == DBNull.Value ? null : Convert.ToString(row["C_DETAILS_CODE"]),
                C_CODE1 = row["C_CODE1"] == DBNull.Value ? null : Convert.ToString(row["C_CODE1"]),
                C_CODE2 = row["C_CODE2"] == DBNull.Value ? null : Convert.ToString(row["C_CODE2"]),
                C_CODE3 = row["C_CODE3"] == DBNull.Value ? null : Convert.ToString(row["C_CODE3"]),
                C_CODE4 = row["C_CODE4"] == DBNull.Value ? null : Convert.ToString(row["C_CODE4"])
            };
            return entity;
        }

        public IResonResult SelectData()
        {
            var sql = "SELECT T1.* FROM QS_PROJECT T1";
            var bll = new BaseBLL();
            var dt = bll.Query(sql);
            //递归排序
            var dt1 = dt.Clone();
            Inno(dt1, dt, 0);
            return new ResonResult("获取成功", ResultType.success, dt1);
        }

        private void Inno(DataTable t1, DataTable t2, int pid)
        {
            foreach (var row in t2.AsEnumerable().Where(a => Convert.ToInt32(a["N_PARENTID"]) == pid).OrderBy(a => Convert.ToInt32(a["N_NUM1"])).ThenBy(a => Convert.ToInt32(a["N_NUM2"])))
            {
                var entity = t1.NewRow();
                entity["N_PROID"] = row["N_PROID"];
                entity["C_PRONAME"] = row["C_PRONAME"];
                entity["N_OPERATORID"] = row["N_OPERATORID"];
                entity["D_DATE"] = row["D_DATE"];
                entity["N_PARENTID"] = row["N_PARENTID"];
                entity["C_REMARK"] = row["C_REMARK"];
                entity["N_NUM1"] = row["N_NUM1"];
                entity["N_NUM2"] = row["N_NUM2"];
                entity["N_STATE"] = row["N_STATE"];
                entity["N_TYPE"] = row["N_TYPE"];
                entity["C_PRONUM"] = row["C_PRONUM"];
                entity["CMY_ID"] = row["CMY_ID"];
                entity["N_TYPE2"] = row["N_TYPE2"];
                t1.Rows.Add(entity);
                Inno(t1, t2, Convert.ToInt32(entity["N_PROID"]));
            }
        }

        public DataTable GetInfoByPId(decimal cmyid, decimal pid)
        {
            var sql = string.Format(@"select n_proid,
       c_proname,
       n_parentid,
       n_state,
       cmy_id,
       n_if_details,
       c_details_code
  from qs_project
 where n_if_details = 1
   and n_state=1
   and cmy_id = {0}
   and n_parentid = {1}", cmyid, pid);
            var dt = Query(sql);
            return dt;
        }

        public QS_PROJECT FindByName(string proName, decimal cmyid)
        {
            var sql = "SELECT * FROM QS_PROJECT WHERE C_PRONAME LIKE '{0}' AND CMY_ID={1} AND n_state=1";
            sql = string.Format(sql, proName, cmyid);
            var row = RowSingeleOfNull(sql);
            if (row == null)
            {
                return null;
            }
            return ToEntity(row);
        }

        #region 公司汇总新增

        public List<QS_PROJECT> GetAllProject()
        {
            var sql = "SELECT * FROM QS_PROJECT";
            var dt = Query(sql);
            var entitys = new List<QS_PROJECT>();
            foreach (var item in dt.AsEnumerable())
            {
                entitys.Add(ToEntity(item));
            }
            return entitys;
        }

        #endregion 公司汇总新增
    }
}